package veiculosLocado;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import util.Conexao;

public class RepositorioVeiculoLocadoBanco implements IVeiculoLocado{
	
	private Connection con;
	
	@Override
	public void adicionar(VeicuLoLocado vLocado) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "insert into \"veiculoLocado\" (\"qtdDeDias\",\"kmInicial\", \"kmFinal\",\"subTotal\",\"idLocacao\",\"idVeiculo\")"+
				"values (?,?,?,?,?,?);";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, vLocado.getQuantidaDeDias());
		ps.setDouble(2, vLocado.getKmInical());
		ps.setDouble(3, vLocado.getKmFinal());
		ps.setDouble(4, vLocado.getSubTotal());
		ps.setInt(5, vLocado.getIdLocacao());
		ps.setInt(6, vLocado.getVeiculo().getId());
		ps.execute();
	}

	@Override
	public void editar(VeicuLoLocado vLocado) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "update \"veiculoLocado\" set \"qtdDeDias\" = ?, \"kmInicial\" = ?, \"kmFinal\" = ? ,"+
				"\"subTotal\" = ?, \"idLocacao\" = ?, \"idVeiculo\" = ? where id = ?;";	
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, vLocado.getQuantidaDeDias());
		ps.setDouble(2, vLocado.getKmInical());
		ps.setDouble(3, vLocado.getKmFinal());
		ps.setDouble(4, vLocado.getSubTotal());
		ps.setInt(5, vLocado.getIdLocacao());
		ps.setInt(6, vLocado.getVeiculo().getId());
		ps.setInt(7, vLocado.getId());
		ps.execute();
	}

	@Override
	public void remover(int idVLocado) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "delete from \"veiculoLocado\"  where id = ?;";	
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, idVLocado);
		ps.execute();
	}

	@Override
	public int existe(int idVlocado) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		int res = 0;
		VeicuLoLocado vLoc = pesquisar(idVlocado);
		if(vLoc != null){
			res = 1;
		}
		return res;
	}

	@Override
	public VeicuLoLocado pesquisar(int idVLocado) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "select * from \"veiculoLocado\" where id= ?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, idVLocado);
		ps.execute();
		ResultSet rs = ps.getResultSet();
		VeicuLoLocado vLoc = null;
		if(rs.next()){
			vLoc = new VeicuLoLocado();
			vLoc.setId(rs.getInt("id"));
			vLoc.setQuantidaDeDias(rs.getInt("qtdDeDias"));
			vLoc.setKmInical(rs.getDouble("kmInicial"));
			vLoc.setKmFinal(rs.getDouble("kmFinal"));
			vLoc.setSubTotal(rs.getDouble("subTotal"));
			vLoc.setIdLocacao(rs.getInt("idLocacao"));
			vLoc.getVeiculo().setId(rs.getInt("idVeiculo"));
		}
		return vLoc;
	}

	@Override
	public ArrayList<VeicuLoLocado> listar() throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "select * from \"veiculoLocado\";";
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery(sql);
		ArrayList<VeicuLoLocado> vLocs = new ArrayList<VeicuLoLocado>();
		while(rs.next()){
			VeicuLoLocado vLoc = new VeicuLoLocado();
			vLoc.setId(rs.getInt("id"));
			vLoc.setQuantidaDeDias(rs.getInt("qtdDeDias"));
			vLoc.setKmInical(rs.getDouble("kmInicial"));
			vLoc.setKmFinal(rs.getDouble("kmFinal"));
			vLoc.setSubTotal(rs.getDouble("subTotal"));
			vLoc.setIdLocacao(rs.getInt("idLocacao"));
			vLoc.getVeiculo().setId(rs.getInt("idVeiculo"));
			vLocs.add(vLoc);
		}
		return vLocs;
	}

	@Override
	public ArrayList<VeicuLoLocado> listar(int IdLocacao) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "select * from \"veiculoLocado\" where \"idLocacao\"= ?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, IdLocacao);
		ps.execute();
		ResultSet rs = ps.getResultSet();
		ArrayList<VeicuLoLocado> vLocs = new ArrayList<VeicuLoLocado>();
		while(rs.next()){
			VeicuLoLocado vLoc = new VeicuLoLocado();
			vLoc.setId(rs.getInt("id"));
			vLoc.setQuantidaDeDias(rs.getInt("qtdDeDias"));
			vLoc.setKmInical(rs.getDouble("kmInicial"));
			vLoc.setKmFinal(rs.getDouble("kmFinal"));
			vLoc.setSubTotal(rs.getDouble("subTotal"));
			vLoc.setIdLocacao(rs.getInt("idLocacao"));
			vLoc.getVeiculo().setId(rs.getInt("idVeiculo"));
			vLocs.add(vLoc);
			
		}
		return vLocs;
	}

	@Override
	public int VeiculoJaFoiLocado(int idVeiculo) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "	select count(vl.id) from \"veiculoLocado\" vl, veiculo v where "+
				"vl.\"idVeiculo\" = v.id andv.id = 3;";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, idVeiculo);
		ps.execute();
		int res = 0;
		ResultSet rs = ps.getResultSet();
		if(rs.next()){
			res = 1;
		}
		return res;
	}

}
